﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using YXQ.Common.Meta;

namespace YXQ.Common.Helper
{
    public class DBHelper
    {
        public static Database Db
        {
            get { 
                return DatabaseFactory.CreateDatabase("DB"); 
            }
        }

 

        

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="sql">T-SQL查询语句</param>
        /// <param name="currentIndex">当前页号（从0开始）</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>IDataReader</returns>
        public static IDataReader GetPageData(string sqlStr, int pageIndex, int pageSize, out int rowCount)
        {
            DbCommand cmd = Db.GetSqlStringCommand(@"declare @p1 int

                                                    declare @currentPage int
                                                    set @currentPage = 0 
                                                    declare @RowCount int
                                                    set @RowCount = 0 
                                                    declare @PageCount int
                                                    set @PageCount = 0 

                                                    exec sp_cursoropen 
                                                    @p1 output,@strSql,
                                                    @scrollopt=1,
                                                    @ccopt=1,
                                                    @rowcount=@rowCount 
                                                    output --得到总记录数

                                                    select @PageCount=ceiling(1.0*@rowCount/@pagesize)  --得到总页数
                                                    ,@currentPage=(@PageIndex-1)*@PageSize+1
                                                    select @RowCount,@PageCount
                                                    exec sp_cursorfetch @p1,16,@currentPage,@PageSize 
                                                    exec sp_cursorclose @p1");

            Db.AddInParameter(cmd, "StrSql", DbType.AnsiString, sqlStr);
            Db.AddInParameter(cmd, "PageIndex", DbType.Int32, pageIndex + 1);

            Db.AddInParameter(cmd, "PageSize", DbType.Int32, pageSize);
            //  db.AddInParameter(cmd, "rowcount", DbType.Int32, 4);
            IDataReader dataReader = Db.ExecuteReader(cmd);
            if (dataReader.NextResult())
            {
                dataReader.Read();
                rowCount = dataReader.GetInt32(0);
                dataReader.NextResult();
            }
            else
            {
                rowCount = 0;
                dataReader = null;
            }
            return dataReader;
        }

         

        /// <summary>
        /// 返回DATATABLE
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="db">数据库连接</param>
        /// <param name="parameters">参数数组</param>
        /// <returns></returns>
        public static DataTable ExecuteDataSet(string sql, List<Parameter> parameters)
        {
            DataTable dataTable = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    dataTable = Db.ExecuteDataSet(dbCommand).Tables[0];

                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteDataSet(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql);
                    throw ex;
                }


            }
            return dataTable;

        }


        public static DataSet RtnExecuteDataSet(string sql, List<Parameter> parameters)
        {
            DataSet ds = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    ds = Db.ExecuteDataSet(dbCommand);

                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法RtnExecuteDataSet(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }
            }
            return ds;

        }


        /// <summary>
        /// 返回datatable 不带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataSet(string sql)
        {
            DataTable dataTable = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    dataTable = Db.ExecuteDataSet(dbCommand).Tables[0];

                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteDataSet(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }

            }
            return dataTable;

        }

        /// <summary>
        /// 将分页后的数据填充到DataTable.
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="currentIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetPageDataTable(string sql, int currentIndex, int pageSize, out int rowCount)
        {
            IDataReader reader = GetPageData(sql, currentIndex, pageSize, out rowCount);
            DataTable tab = new DataTable("data");
            tab.Load(reader);
            return tab;
        }



       

        /// <summary>
        /// 返回执行查询的记录条数

        /// </summary>
        /// <param name="sql"></param>
        /// <param name="db"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, List<Parameter> parameters)
        {
            int recordcount = 0;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    recordcount = Db.ExecuteNonQuery(dbCommand);
                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteNonQuery(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }

            }
            return recordcount;
        }


        /// <summary>
        /// 返回记录条数  不带参数数组
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            int recordcount = 0;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    recordcount = Db.ExecuteNonQuery(dbCommand);
                }
                catch (Exception ex)
                {

                    LogHelper.Error("方法ExecuteNonQuery(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw;
                }

            }
            return recordcount;
        }

        public static object ExecuteScalar(string sql)
        {
            object obj = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                try
                {
                    obj = Db.ExecuteScalar(dbCommand);
                }
                catch (Exception ex)
                {
                    LogHelper.Error("方法ExecuteScalar(不带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql);
                    throw ex;


                }

            }
            return DataHelper.GetInteger(obj);
        }
        public static object ExecuteScalar(string sql,  List<Parameter> parameters)
        {
            object obj = null;
            using (DbCommand dbCommand = Db.GetSqlStringCommand(sql))
            {
                BuildePrm(parameters, dbCommand, Db);
                try
                {
                    obj = Db.ExecuteScalar(dbCommand);
                }
                catch (System.Exception ex)
                {
                    LogHelper.Error("方法ExecuteScalar(带参数数组)执行错误：" + ex.StackTrace + " \n 出错SQL:" + sql, ex);
                    throw ex;
                }

            }
            return DataHelper.GetInteger(obj);
        }

        /// <summary>
        /// 组合参数
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="dbCommand"></param>
        /// <param name="db"></param>
        public static void BuildePrm(List<Parameter> parameters, DbCommand dbCommand, Database db)
        {
            foreach (var parameter in parameters)
            {
                db.AddInParameter(dbCommand, parameter.ValuePrm, parameter.DType, parameter.Value);
            }
        }
    }
}
